package com.zbvc.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import com.zbvc.dao.BookDao;
import com.zbvc.entity.Admin;
import com.zbvc.entity.Book;
import com.zbvc.entity.History;
import com.zbvc.util.DBUtil;

public class BookDaoImpl implements BookDao {
    /*
     * 查询所有书籍
     */
	@Override
	public List<Book> findAllBooks() {
		
		String sql = " SELECT * FROM book ";
		Connection conn = DBUtil.getConnect();
		// 定义一个泛型为Book的集合
		List<Book> lists = new ArrayList<Book>();
		try {
			PreparedStatement pstm = conn.prepareStatement(sql);
			ResultSet rs = pstm.executeQuery();
			while(rs.next()) {
				//将每一条书籍信息存入List集合
				Book book = new Book();
				book.setBid(rs.getInt("bid"));
				book.setName(rs.getString("name"));
				book.setAuthor(rs.getString("author"));
				book.setCard(rs.getString("card"));
				book.setPress(rs.getString("press"));
				book.setNum(rs.getInt("num"));
				book.setType(rs.getString("type"));
				lists.add(book);
			}
			return lists; // 返回集合
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
    /*
     * 根据id删除数据
     */
	@Override
	public int deleteBookById(int bid) {
		
		String sql = "DELETE FROM book WHERE bid=? ";
		Connection conn = DBUtil.getConnect();
		PreparedStatement pstm = null;
		int row = 0;
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, bid);
			row = pstm.executeUpdate();
			return row;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return 0;
	}
	@Override
	public List<Book> findLikeBooks(String name) {
		String sql = "SELECT * FROM book WHERE name LIKE '%" + name + "%' ";
		Connection conn = DBUtil.getConnect();
		Statement stm = null;
		ResultSet rs = null;
		List<Book> list = new ArrayList<Book>();
		try {
			stm = conn.createStatement();
			rs = stm.executeQuery(sql);
			while (rs.next()) {
				Book book = new Book();
				book.setBid(rs.getInt("bid"));
				book.setName(rs.getString("name"));
				book.setCard(rs.getString("card"));
				book.setAuthor(rs.getString("author"));
				book.setNum(rs.getInt("num"));
				book.setPress(rs.getString("press"));
				book.setType(rs.getString("type"));
				list.add(book);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	@Override
	public int insertBook(Book book) {
		String sql = "INSERT INTO book (name,card,author,num,press,type) VALUES (?,?,?,?,?,?) ";
		Connection conn = DBUtil.getConnect();
		PreparedStatement pstm = null;
		int row = 0;
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setString(2, book.getCard());
			pstm.setString(1, book.getName());
			pstm.setString(3, book.getAuthor());
			pstm.setString(5, book.getPress());
			pstm.setInt(4, book.getNum());
			pstm.setString(6, book.getType());
			row = pstm.executeUpdate();
			return row;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return row;
	}
	@Override
	public boolean findBookByCard(String card) {
		Connection conn = DBUtil.getConnect();
		String sql = "SELECT * FROM book WHERE card=?";
		try {
			PreparedStatement pstm = conn.prepareStatement(sql);
			pstm.setString(1, card);
			ResultSet rs = pstm.executeQuery();
			if(rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	/*
	 * sql语句对象 Statement PreparedStatement
	 * 
	 * 
	 */
	@Override
	public List<Book> findBookByName(String name) {

		String sql = " SELECT * FROM book WHERE name LIKE '%"+name+"%'";
		Connection conn = DBUtil.getConnect();
		// 定义一个泛型为Book的集合
		List<Book> lists = new ArrayList<Book>();
		try {
			Statement stm = conn.createStatement();
			ResultSet rs = stm.executeQuery(sql);
			while(rs.next()) {
				//将每一条书籍信息存入List集合
				Book book = new Book();
				book.setBid(rs.getInt("bid"));
				book.setName(rs.getString("name"));
				book.setAuthor(rs.getString("author"));
				book.setCard(rs.getString("card"));
				book.setPress(rs.getString("press"));
				book.setNum(rs.getInt("num"));
				book.setType(rs.getString("type"));
				lists.add(book);
			}
			return lists; // 返回集合
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	@Override
	public int updateBookById(Book book) {
		Connection conn = DBUtil.getConnect();
		String sql = "UPDATE book SET name=?,press=?,author=?,num=? WHERE bid=?";
		try {
			PreparedStatement pstm = conn.prepareStatement(sql);
			pstm.setString(1, book.getName());
			pstm.setString(2, book.getPress());
			pstm.setString(3, book.getAuthor());
			pstm.setInt(4, book.getNum());
			pstm.setInt(5, book.getBid());
			int row = pstm.executeUpdate();
			return row;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	@Override
	public Book findBookById(int bid) {
		Connection conn = DBUtil.getConnect();
		String sql = "SELECT * FROM book WHERE bid=?";
		try {
			PreparedStatement pstm = conn.prepareStatement(sql);
			pstm.setInt(1, bid);
			ResultSet rs = pstm.executeQuery();
			if(rs.next()) {
				Book book = new Book();
				book.setBid(rs.getInt("bid"));
				book.setName(rs.getString("name"));
				book.setAuthor(rs.getString("author"));
				book.setCard(rs.getString("card"));
				book.setPress(rs.getString("press"));
				book.setNum(rs.getInt("num"));
				book.setType(rs.getString("type"));
				return book;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	@Override
	public int borrowBook(int bid, Admin admin) {
		Connection conn = DBUtil.getConnect();
		String sql = "INSERT INTO history (aid,bid,card,bookname,adminname,username,begintime,endtime,status) "
				+ "VALUES (?,?,?,?,?,?,?,?,?)";
		// 通过bid查询当前需要借阅的图书数信息  this代表当前类对象
		Book book = this.findBookById(bid);
		Calendar c = Calendar.getInstance();  // 格式  yyyy-MM-dd
		int year = c.get(Calendar.YEAR);
		int month = c.get(Calendar.MONTH)+1;
		int day = c.get(Calendar.DATE);
		// 开始时间
		String begintime = ""+year+"-"+month+"-"+day;
		c.add(Calendar.MONDAY, 3);
		// 借书时间，从开始时间往后推迟2个月
		String endtime = ""+c.get(Calendar.YEAR)+"-"+c.get(Calendar.MONTH)+"-"+c.get(Calendar.DATE);
		try {
			PreparedStatement pstm = conn.prepareStatement(sql);
			pstm.setInt(1, admin.getAid());
			pstm.setInt(2, bid);
			pstm.setString(3, book.getCard());
			pstm.setString(4, book.getName());
			pstm.setString(5, admin.getUsername());
			pstm.setString(6, admin.getName());
			pstm.setString(7, begintime);
			pstm.setString(8, endtime);
			pstm.setInt(9, 1);
			int row = pstm.executeUpdate();
			return row;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return 0;
	}
	/**
	 * 查询某读者的借阅信息
	 */
	@Override
	public List<History> findAllHistory(int status, int aid) {
		Connection conn = DBUtil.getConnect();
		String sql = "SELECT * FROM history WHERE status=? AND aid=?";
		List<History> lists = new ArrayList<>();
		try {
			PreparedStatement pstm = conn.prepareStatement(sql);
			pstm.setInt(1, status);
			pstm.setInt(2, aid);
			ResultSet rs = pstm.executeQuery();
			while(rs.next()) {
				History his = new History();
				his.setHid(rs.getInt("hid"));
				his.setAid(rs.getInt("aid"));
				his.setBid(rs.getInt("bid"));
				his.setCard(rs.getString("card"));
				his.setUsername(rs.getString("username"));
				his.setAdminname(rs.getString("adminname"));
				his.setBegintime(rs.getString("begintime"));
				his.setEndtime(rs.getString("endtime"));
				his.setStatus(rs.getInt("status"));
				his.setBookname(rs.getString("bookname"));
				lists.add(his);
			}
			return lists;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	@Override
	public int backBook(int hid) {
		Connection conn = DBUtil.getConnect();
		String sql = "UPDATE history SET status=? WHERE hid=?";
		try {
			PreparedStatement pstm = conn.prepareStatement(sql);
			pstm.setInt(1, 0);
			pstm.setInt(2, hid);
			int row = pstm.executeUpdate();
			return row;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return 0;
	}

}














